package org.example.musicproject.dao.impl;

import org.example.musicproject.dao.SingerAddsongsDao;
import org.example.musicproject.entity.SingerAddsongs;
import org.example.musicproject.util.DButil;

import java.sql.*;
import java.util.Collections;
import java.util.List;

/**
 * 创建人:黄庭萍
 * 创建时间: 2024/12/10 20:46
 * 创建类型: SingerAddsongsDaoImpl 歌曲歌手关系绑定数据实现类
 */
public class SingerAddsongsDaoImpl implements SingerAddsongsDao {
    @Override
    public List<SingerAddsongs> all() {
        String sql = "SELECT a.id AS soid,a.name AS soname,b.name AS siname,a.introduction,a.createDate,\n" +
                "a.updateDate,a.pic,a.lyric,a.url,a.mvurl,a.isVip,a.num FROM  song a\n" +
                "LEFT JOIN singerAddsongs ss ON a.id = ss.soid\n" +
                "LEFT JOIN singer b ON ss.ssid = b.id;";
        System.out.println("=====查询所有歌曲歌手关系绑定信息============");
        System.out.println("sql="+sql);
        System.out.println("================================");
        return DButil.list(sql, SingerAddsongs.class);
    }

    @Override
    public SingerAddsongs byId(int ssid) {
        String sql="SELECT a.id AS soid,a.name AS soname,b.name AS siname,a.introduction,a.createDate,\n" +
                "a.updateDate,a.pic,a.lyric,a.url,a.mvurl,a.isVip,a.num FROM  song a\n" +
                "LEFT JOIN singerAddsongs ss ON a.id = ss.soid\n" +
                "LEFT JOIN singer b ON ss.ssid = b.id where ss.ssid=?";
        System.out.println("=====根据id查询歌曲歌手关系绑定信息============");
        System.out.println("sql="+sql);
        System.out.println("ssid="+ssid);
        System.out.println("================================");
        return DButil.one(sql, SingerAddsongs.class, ssid);
    }

    @Override
    public List<SingerAddsongs> allname(int ssid) {
        String sql = "SELECT a.id AS soid,a.name AS soname,b.name AS siname,a.introduction,a.createDate,\n" +
                "a.updateDate,a.pic,a.lyric,a.url,a.mvurl,a.isVip,a.num FROM  song a\n" +
                "LEFT JOIN singerAddsongs ss ON a.id = ss.soid\n" +
                "LEFT JOIN singer b ON ss.ssid = b.id where ss.ssid=?";

        System.out.println("=====查询歌曲及其歌手信息(列出所有歌曲和演唱这些歌曲的歌手)============");
        System.out.println("sql=" + sql);
        System.out.println("name=" + ssid);
        System.out.println("================================");
        return  DButil.list(sql, SingerAddsongs.class,ssid);
    }

    @Override
    public long add(Connection conn, SingerAddsongs singerAddsongs) {
        String sql = "insert into singerAddsongs (soid,siid) values(?,?)";
        long id = 0l;
        try(PreparedStatement ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS)){
            ps.setInt(1,singerAddsongs.getSoid());
            ps.setInt(2,singerAddsongs.getSiid());

            int rows = ps.executeUpdate();
            if (rows == 0) {
                throw  new SQLException("歌曲歌手关系绑定添加失败!");
            }
            try(ResultSet rs = ps.getGeneratedKeys()) {
                if (rs.next()) {
                    id = rs.getLong(1);
                    System.out.println("生成歌曲歌手关系绑定密码:"+id);
                }else {
                    throw  new SQLException("歌曲歌手关系绑定添加失败!没有生成歌曲歌手关系绑定ID编号");
                }
            }
        }catch (SQLException e){
            throw  new RuntimeException("歌曲歌手关系绑定编号添加失败!原因:"+e.getMessage());
        }
        System.out.println("=====根据id添加歌手信息========");
        System.out.println("sql=" + sql);
        System.out.println("conn=" + conn);
        System.out.println("singAddsongs="+singerAddsongs);
        System.out.println("========================");
        return id;
    }

    @Override
    public int update(SingerAddsongs singerAddsongs) {
        String sql="update singerAddsongs set soid = ?,siid = ? where ssid = ?";
        System.out.println("=====修改歌曲歌手关系绑定信息========");
        System.out.println("sql=" + sql);
        System.out.println("singAddsongs="+singerAddsongs);
        System.out.println("========================");
        return DButil.update(sql,singerAddsongs.getSoid(),singerAddsongs.getSiid(),singerAddsongs.getSsid());
    }
}
